package tw.com.softleader.reins.demo.coffee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import tw.com.softleader.reins.demo.coffee.entity.Coffee;

@Repository
public class CoffeeDAOImplement implements CoffeeDAO {
	
	@Autowired
	private DataSource dataSource;
	
	private final String SELECT_ALL = "SELECT * FROM coffee";

	@Override
	public List<Coffee> selectAll() {

		List<Coffee> coffeeList = new ArrayList<Coffee>();

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		
		try {
			conn = dataSource.getConnection();
			
			stmt = conn.prepareStatement(SELECT_ALL);
			rs = stmt.executeQuery();

			while (rs.next()) {
				Coffee coffee = new Coffee();
				coffee.setId(rs.getLong("id"));
				coffee.setBrand(rs.getString("brand"));
				coffee.setType(rs.getString("type"));
				coffee.setSize(rs.getString("size"));
				coffee.setPrice(rs.getDouble("price"));
				
				coffeeList.add(coffee);



			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		
		
		return coffeeList;
	}

	private final String SELECT_BY_ID = "SELECT * FROM coffee WHERE id = ?";
	@Override
	public Coffee selectById(Long id) {
		Coffee coffee = new Coffee();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_ID);
			stmt.setLong(1, id);
			rs = stmt.executeQuery();

			rs.next();
			
			coffee.setId(rs.getLong("id"));
			coffee.setBrand(rs.getString("brand"));
			coffee.setType(rs.getString("type"));
			coffee.setSize(rs.getString("size"));
			coffee.setPrice(rs.getDouble("price"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		
		return coffee;
	}

	
	private final String INSERT ="insert into coffee (brand, type, size, price) values (?, ?, ?, ?)";
	@Override
	public Integer insert(Coffee coffee) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(INSERT);
			
			//stmt.setLong(1, coffee.getId());
			stmt.setString(1, coffee.getBrand());
			stmt.setString(2, coffee.getType());
			stmt.setString(3, coffee.getSize());
			stmt.setDouble(4, coffee.getPrice());
			return stmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	
	}

	
	private final String UPDATE = "UPDATE coffee SET brand = ?, type = ?, size = ?, price = ? WHERE id = ?";
	@Override
	public Integer update(Coffee coffee) {
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(UPDATE);
			stmt.setString(1, coffee.getBrand());
			stmt.setString(2, coffee.getType());
			stmt.setString(3, coffee.getSize());
			stmt.setDouble(4, coffee.getPrice());
			stmt.setLong(5, coffee.getId());
			return stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private final String DELETE = "DELETE FROM coffee WHERE id = ?";
	@Override
	public Integer delete(Long id) {
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(DELETE);
			stmt.setLong(1, id);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}
